﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO

Public Class LichGanDao
    Dim provider As New DataProvider

    Public Function LayDanhSachLichGan() As DataTable
        Dim sql As String = "SELECT * FROM LichGan"

        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function TimLichGanTheoMa(ByVal maCanTim As Integer) As DataTable
        Dim sql As String = "SELECT * FROM LichGan WHERE MaLichGan = " & maCanTim

        Return provider.ThucThiCauTruyVan(sql)
    End Function

    'Tìm lịch gán theo mã tuyến, ngày khởi hành, thời điểm và xe
    Public Function TimLichGan(ByVal maTuyen As Long, ByVal ngayKhoiHanh As Date, ByVal thoiDiemKhoiHanh As String, ByVal maXe As Long) As DataTable
        Dim sql As String = "SELECT * FROM LichGan WHERE MaTuyen = ? and NgayKhoiHanh = ? and ThoiDiemKhoiHanh = ? and MaXe = ?"
        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", maTuyen))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", ngayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", thoiDiemKhoiHanh))
        dsParameter.Add(New OleDbParameter("@MaXe", maXe))
        Return provider.ThucThiCauTruyVan(sql, dsParameter)

    End Function

    Public Function ThemMotLichGan(ByVal lg As LichGanDto) As Integer
        Dim sql As String = "INSERT  INTO LichGan( MaTuyen, ThoiDiemKhoiHanh, NgayKhoiHanh, MaXe, MaNhanVien) VALUES ( ?, ?, ?, ?, ?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", lg.MaTuyen))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", lg.ThoiDiemKhoiHanh))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", lg.NgayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@MaXe", lg.MaXe))
        dsParameter.Add(New OleDbParameter("@MaNhanVien", lg.MaNhanVien))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)

    End Function

    Public Function CapNhatLichGan(ByVal lg As LichGanDto) As Integer
        Dim sql As String = "UPDATE LichGan SET MaLichGan = ?, MaTuyen = ?, ThoiDiemKhoiHanh = ?, NgayKhoiHanh = ?, MaXe = ?, MaLap = ? WHERE MaLichGan = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", lg.MaTuyen))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", lg.ThoiDiemKhoiHanh))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", lg.NgayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@MaXe", lg.MaXe))
        dsParameter.Add(New OleDbParameter("@MaNhanVien", lg.MaNhanVien))
        dsParameter.Add(New OleDbParameter("@MaLichGan", lg.MaLichGan))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)

    End Function

    Public Function XoaMotLichGan(ByVal maCanXoa As Long) As Integer
        Dim sql As String = "DELETE FROM LichGan WHERE MaLichGan = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaLichGan", maCanXoa))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    'Tìm ngày khởi hành
    Public Function TimNgayKhoiHanh(ByVal maTuyen As Integer) As DataTable
        Dim sql As String = "SELECT NgayKhoiHanh FROM LichGan WHERE MaTuyen = " & maTuyen
        sql += " Group by NgayKhoiHanh  Order by NgayKhoiHanh"

        Return provider.ThucThiCauTruyVan(sql)
    End Function

    'Tìm giờ khởi hành
    Public Function TimThoiDiemKhoiHanh(ByVal maTuyen As Integer, ByVal ngayKhoiHanh As Date) As DataTable
        Dim sql As String = "SELECT ThoiDiemKhoiHanh FROM LichGan WHERE MaTuyen = ? and NgayKhoiHanh = ?"
        sql += " Group by ThoiDiemKhoiHanh Order by ThoiDiemKhoiHanh"
        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", maTuyen))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", ngayKhoiHanh))

        Return provider.ThucThiCauTruyVan(sql, dsParameter)
    End Function

    'Tìm xe đang rảnh theo ngày và thời điểm khởi hành
    Public Function DanhSachXeDangRanh(ByVal ngayKhoiHanh As Date, ByVal ThoiDiemKhoiHanh As String) As DataTable
        Dim sql As String = "Select * From XE Where MaXe not in("
        sql += " Select lg.MaXe From LichGan lg Where lg.NgayKhoiHanh = ? and lg.ThoiDiemKhoiHanh = ? )"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", ngayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", ThoiDiemKhoiHanh))

        Return provider.ThucThiCauTruyVan(sql, dsParameter)
    End Function

    'Lấy mã lịch gán vừa thêm vào
    Public Function LayMaLichGanVuaThem() As Long
        Dim sql As String = "Select max(MaLichGan)as MaLichGan From LichGan"
        Dim dt As DataTable = provider.ThucThiCauTruyVan(sql)
        Return dt.Rows(0).Item(0)
    End Function

    'Bán vé...

    'Tìm xe theo tuyến, thời điểm
    Public Function DanhSachXe_TheoTuyen_ThoiDiem(ByVal maTuyen As Long, ByVal ngayKhoiHanh As Date, ByVal ThoiDiemKhoiHanh As String) As DataTable
        Dim sql As String = "SELECT x.MaXe, x.SoXe, x.SoChoNgoi FROM XE x, LichGan lg"
        sql += " WHERE lg.MaXe = x.MaXe and lg.MaTuyen = ? and lg.NgayKhoiHanh = ? and lg.ThoiDiemKhoiHanh = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", maTuyen))
        dsParameter.Add(New OleDbParameter("@NgayKhoiHanh", ngayKhoiHanh))
        dsParameter.Add(New OleDbParameter("@ThoiDiemKhoiHanh", ThoiDiemKhoiHanh))

        Return provider.ThucThiCauTruyVan(sql, dsParameter)
    End Function

End Class
